package com.cn.bulu.TEST;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

import org.junit.Test;

/**
 * 
 * @author zhaochaojie
 *
 */
public class initialDataBase {
	DBHelp db = new DBHelp();

	private static ExecutorService executorService = new ThreadPoolExecutor(28, 35, 0L, TimeUnit.MILLISECONDS,
			new LinkedBlockingQueue<Runnable>(100000), new ThreadPoolExecutor.CallerRunsPolicy());

	@Test
	public void test1() throws Exception {
		// 查询所有的省份
		String queryProvinces = "select territoryId from territory";
		Connection queryProvincesCon = db.getCon();
		PreparedStatement statement = queryProvincesCon.prepareStatement(queryProvinces);
		ResultSet executeQuery = statement.executeQuery();
		while (executeQuery.next()) {
			Integer territoryId = executeQuery.getInt("territoryId");
			String type = "";
			for (int j = 0; j < 8; j++) {
				switch (j) {
				case 0:
					type = "罗马杆";
					break;
				case 1:
					type = "直轨";
					break;
				case 2:
					type = "壁钩";
					break;
				case 3:
					type = "挂绳";
					break;
				case 4:
					type = "魔术轨";
					break;
				case 5:
					type = "弯轨";
					break;
				case 6:
					type = "四爪钩";
					break;
				case 7:
					type = "电机";
					break;
				default:
					break;
				}
				String sql = "";
				if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else if ("罗马杆".equals(type)) {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				} else {
					sql = "insert into cost_rules(single,doubleDeck,basisMeter,beyondMeter,meterPrice,type,motorNumber,territoryId) values()";
				}
				queryProvincesCon.prepareStatement(sql);
			}
			System.out.println();
		}
		db.closeAll(queryProvincesCon, statement, executeQuery);
	}

	// 初始化鹿客帮规则
	@Test
	public void test4() throws Exception {
		// 查询所有的省份
		String insertSql = "truncate table bulu_merchantAppRules;INSERT INTO bulu_merchantAppRules  (`id`, `summary`, `serveScope`, `indemnifyScope`, `indemnifyLimit`, `requiredInfo`, `claimTime`, `declareContent`) VALUES ('1',?,?,?,?,?,?,?)";
		Connection insertConn = db.getCon();
		PreparedStatement statement = insertConn.prepareStatement(insertSql);
		statement.setString(1,
				"您在鹿客帮发布的测量、安装、维修订单并支付给工人师傅的费用（包括悬赏加价）本平台不进行提成或赚取差价（测量、安装、维修费用的单价为鹿客帮根据各地区工人师傅的实际收费确定，您使用鹿客帮发布订单信息即视为接受该单价，且该单价将根据实际情况进行变更，恕不另行通知）。为了保障用户权益，请选择安心宝增值服务。安心宝是鹿客帮为用户在本平台所发布的的测量、安装、维修订单提供的一项增值服务，该增值服务默认选中。您在鹿客帮发布的订单如工人师傅在提供服务的过程中造成损失的，鹿客帮将予以赔偿。从而使您的权益得到保障（未选择安心宝的用户鹿客帮将不对其使用鹿客帮平台造成的任何损失负责）。");
		statement.setString(2, "测量订单、安装订单、维修订单。");
		statement.setString(3, "因工人责任对鹿客帮用户造成的损失。");
		statement.setString(4, "500");
		statement.setString(5, "用户的损失证明材料。");
		statement.setString(6, "测量订单在订单完成20日内（包含休息日），安装订单在订单完成24小时内。");
		statement.setString(7, "鹿客帮特此声明，本「安心宝」增值服务并非保险服务，而鹿客帮亦不会提供任何保险服务。如有需要，您可选择自行投保，否则用户将承担一切风险。");
		statement.executeUpdate();
	}

	// 添加worker表的字段 包括workerSpaceStr provinceCode cityCode
	@Test
	public void test5() throws Exception {
		Connection connection = db.getCon();
		Statement stmt = connection.createStatement();
		String sql0 = "select workerId from worker";
		ResultSet set = stmt.executeQuery(sql0);
		int num = 0;
		while (set.next()) {
			num++;
		}
		System.out.println("num=" + num);
		for (int i = 0; i < num; i++) {
			String sql1 = "select * from worker limit " + i + ",1";
			ResultSet set1 = stmt.executeQuery(sql1);
			int workerId = -1;
			String workerSpace = "";
			String workerSpaceCodeStrings = "";
			String province = "";
			String city = "";
			while (set1.next()) {
				workerId = set1.getInt("workerId");
				workerSpace = set1.getString("workerSpace");
				workerSpaceCodeStrings = set1.getString("workerSpaceCodeStr");
				System.out.println("workerId=" + workerId + ",workerSpace=" + workerSpace + ",workerSpaceCodeString="
						+ workerSpaceCodeStrings);
			}
			if (workerSpace == null) {
				continue;
			}
			if (workerSpaceCodeStrings != null) {
				continue;
			}
			if (workerSpace.indexOf("省") != -1 && workerSpace.indexOf("省") != workerSpace.length() - 1) {
				province = workerSpace.substring(0, workerSpace.indexOf("省") + 1);
				city = workerSpace.substring(workerSpace.indexOf("省") + 1, workerSpace.length());
			} else if (workerSpace.indexOf("区") != -1 && workerSpace.indexOf("区") != workerSpace.length() - 1) {
				province = workerSpace.substring(0, workerSpace.indexOf("区") + 1);
				city = workerSpace.substring(workerSpace.indexOf("区") + 1, workerSpace.length());
			} else if (workerSpace.indexOf("市") != -1 && workerSpace.indexOf("市") != workerSpace.length() - 1) {
				province = workerSpace.substring(0, workerSpace.indexOf("市") + 1);
				city = workerSpace.substring(workerSpace.indexOf("市") + 1, workerSpace.length());
			}
			if ("襄樊市".equals(city)) {
				city = "襄阳市";
			}
			System.out.println("province=" + province + ",city=" + city);

			int provinceCode = -1;
			String sql3 = "select code from chinaAreaTwo where name=" + "\"" + province + "\"" + "and level = 1";
			ResultSet set2 = stmt.executeQuery(sql3);
			while (set2.next()) {
				provinceCode = set2.getInt("code");
			}
			int cityCode = -1;
			String sql4 = "select code from chinaAreaTwo where name=" + "\"" + city + "\"" + "and level = 2";
			ResultSet set3 = stmt.executeQuery(sql4);
			while (set3.next()) {
				cityCode = set3.getInt("code");
			}
			String workerSpaceCodeString = provinceCode + "/" + cityCode;
			System.out.println("workerSpaceCodeString=" + workerSpaceCodeString + ",provinceCode=" + provinceCode
					+ ",cityCode=" + cityCode);
			String sql2 = "update worker set workerSpaceCodeStr=" + "\"" + workerSpaceCodeString + "\""
					+ ",provinceCode=" + provinceCode + ",cityCode=" + cityCode + " where workerId=" + workerId;
			stmt.executeUpdate(sql2);
		}
	}
	// 添加worker_workerSpace表 workerSpaceCode字段
	@Test
	public void test6() throws Exception {
		String sql0 = "select workerId,workerSpace from worker_workerSpace";
		Connection connection1 = db.getCon();
		PreparedStatement prepareStatement1 = connection1.prepareStatement(sql0);
		ResultSet executeQuery = prepareStatement1.executeQuery();
		long start = System.currentTimeMillis();
		while (executeQuery.next()) {
			final int workerId = executeQuery.getInt("workerId");
			final String workerSpace = executeQuery.getString("workerSpace");
			System.out.println("workerId=" + workerId + ",workerSpace=" + workerSpace);
			executorService.submit(new Runnable() {
				@Override
				public void run() {
					Connection connection = null;
					try {
						connection = db.getCon();
						if (workerSpace == null) {
							return;
						}
						// 该工人所在城市code
						String sql2 = "select cityCode from worker where workerId =" + workerId;
						PreparedStatement prepareStatement2 = connection.prepareStatement(sql2);
						ResultSet set2 = prepareStatement2.executeQuery();
						String cityCode = "";
						if (set2.next()) {
							cityCode = set2.getString("cityCode");
						}
						// 工人工作区域code查询
						System.out.println("开始执行sql3");
						String sql3 = "select code from chinaAreaTwo where parentId=" + "'" + cityCode + "'"
								+ " and name=" + "\"" + workerSpace + "\"";
						PreparedStatement prepareStatement3 = connection.prepareStatement(sql3);
						ResultSet set3 = prepareStatement3.executeQuery();
						System.out.println("执行完毕sql3");
						String workerSpaceCode = "";
						while (set3.next()) {
							workerSpaceCode = set3.getString("code");
							System.out.println("workerSpaceCode=" + workerSpaceCode);
						}
						// 插入worker_workerSpace表中
						String sql4 = "update worker_workerSpace set workerSpaceCode = " + "\"" + workerSpaceCode + "\""
								+ " where workerId = " + workerId + " and workerSpace = " + "\"" + workerSpace + "\"";
						PreparedStatement prepareStatement4 = connection.prepareStatement(sql4);
						prepareStatement4.executeUpdate();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			});
		}
		executorService.shutdown(); // 该方法在加入线程队列的线程执行完之前不会执行。
		while (true) {
			if (executorService.isTerminated()) {
				System.out.println("所有的子线程都结束了！");
				long end = System.currentTimeMillis();
				System.out.println("执行时长： " + (end - start));
				break;
			}
		}
	}
}
